package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.jeecgframework.web.system.service.SystemService;

import org.apache.log4j.Logger;
import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.constant.Globals;
import org.jeecgframework.core.util.MyBeanUtils;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import yongxin.dao.equipment.EquipmentMiniDao;
import yongxin.dao.sale.SaleMiniDao;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 设备维修执行表
 * @author mly
 * @date 2015-12-20 21:18:59
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/equipmentMinidaoController")
public class EquipmentMinidaoController extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(EquipmentMinidaoController.class);

	@Autowired
	private EquipmentMiniDao equipmentMiniDao;
	@Autowired
	private SystemService systemService;
	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 设备维修执行表 页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "sbwxzx")
	public ModelAndView sbwxzx(HttpServletRequest request)
	{
		return new ModelAndView("yongxin/kingdeeReport/sbwxzxList");
	}
    
	
	/**
	 * easyui AJAX请求数据  设备维修执行表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "datagrid")
	public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{

		String FRepairWOBillID = request.getParameter("FRepairWOBillID");    // 维修工单号   FDeptName
		
		String FDeptName = request.getParameter("FDeptName");    // 维修工单号 
		String FDeviceName = request.getParameter("FDeviceName");   // 设备名称
		String FPlanBeginDate_begin = request.getParameter("FPlanBeginDate_begin");   // 计划维修开始时间
		String FPlanBeginDate_end = request.getParameter("FPlanBeginDate_end");
		String FPlanEndDate_begin = request.getParameter("FPlanEndDate_begin");   // 计划维修结束日期
		String FPlanEndDate_end = request.getParameter("FPlanEndDate_end");  

String sql = " set nocount on  "; 
sql += " create table #Data  ";                                                      
sql += " (         ";                                                             
sql += " FRepairWOBillID     varchar(255)      null   ";                          
sql += " ,FRepairApplyBillID     varchar(255)  null   ";                          
sql += " ,FRePairPlanBillID  varchar(255)      null  ";                           
sql += " ,FDeviceID  int     null "; //设备ID/表ICDeiveAccount                   
sql += " ,FRepairCheckResultID    int     null ";       //验收结论               
sql += "  ,FSparePartID   int     null "; //备件代码/表t_ICItem                   
sql += " ,FAuxUnitID int    null  "; //常用计量单位/表t_Measureunit              
sql += " ,FAuxQty    decimal(28,10)  default(0) "; //常用单位标准用量            
sql += " ,FAuxQty_Base   decimal(28,10)  default(0) "; //基本单位标准用量        
sql += " ,FUsedQty   decimal(28,10)  default(0) "; //常用单位实际用量            
sql += "  ,FUsedQty_Base  decimal(28,10)  default(0) "; //基本单位实际用量        
sql += " ,FSumsort   int null  ";                                                 
sql += " ,FQtyDecimal decimal(28,10) null default(0)  ) ";                        

sql += " select t1.FRepairWOBillID,isnull(FSParePartID,0) as FSParePartID,FSpaUnit as FSpaUnit,sum(isnull(FAuxQty,0)) as FAuxQty,sum(isnull(FAuxQty_Base,0)) as FAuxQty_Base,sum(isnull(FUsedQty,0)) as FUsedQty,sum(isnull(FUsedQty_Base,0))as FUsedQty_Base  "; 
sql += "  into #data2 from RemoteServer.AIS2009.dbo.icdevicerepairrecord t1 ";                                                                                                                                                          
sql += " left join RemoteServer.AIS2009.dbo.icdevicerepairrecordspare R1  on r1.FID=t1.FID where t1.FBillStatus<>0 group by t1.FRepairWOBillID,r1.FSParePartID,r1.FSpaUnit  ";                                                           

sql += " insert into #data (FRepairWOBillID,FRepairApplyBillID,FRepairPlanBillID,FDeviceID,FRepairCheckResultID,FSparePartID,FAuxUnitID,FAuxQty,FAuxQty_Base,FUsedQty,FUsedQty_Base) ";    
sql += " select t.FBillID as FRepairWOBillID,t.FRepairApplyBillID,t.FRepairPlanBillID,t.FDeviceID,  ";                                                                                                     
sql += " isnull((select distinct FRepairCheckResult from (select FRepairWOBillID,FRepairCheckResult from RemoteServer.AIS2009.dbo.icdevicerepaircheck  ";                                                                              
sql += " where FBIllstatus<>0 and FRepairCheckDate in (select  max(FRepairCheckDate) from  RemoteServer.AIS2009.dbo.icdevicerepaircheck group by FRepairwoBillID)) c  ";                                   
sql += " where t.FBillID = c.FRepairWOBillID) ,0) as FRepairCheckResultID,   ";                                                                                                              
sql += " d2.FSparePartID,d2.FSpaUnit as FAuxUnitID,isnull(t2.FAuxQty,0) as FAuxQty,isnull(t2.FAuxQty_base,0) as FAuxQty_base,isnull(d2.FUsedQty,0) as FUsedQty,isnull(d2.FUsedQty_Base,0) as FUsedQty_Base  ";                                       
sql += " from (select t.* from RemoteServer.AIS2009.dbo.ICDeviceRepairWo t left join RemoteServer.AIS2009.dbo.ICDeviceAccount t2 on t.FDeviceID=t2.FID  where  1=1  ";  

/*sql += "  t2.FDeviceNumber >='YX01-13' " ;
sql += " and t.FBillID >='MNBLL00000037' "; 
sql += " and t.FPlanBeginDate between '2015-01-01' and '2015-12-31' ";
sql += "  and t.FPlanEndDate between '2015-01-01' and '2015-12-31' ";*/
if(StringUtil.isNotEmpty(FRepairWOBillID)){
	sql += " and t.FBillID = '" + FRepairWOBillID + "'";
}
if(StringUtil.isNotEmpty(FDeviceName)){
	sql += " and t2.FDeviceName like '%" + FDeviceName + "%'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_begin)){
	sql += " and t.FPlanBeginDate >= '" + FPlanBeginDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_end)){
	sql += " and t.FPlanBeginDate <= '" + FPlanBeginDate_end + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_begin)){
	sql += " and t.FPlanEndDate >= '" + FPlanEndDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_end)){
	sql += " and t.FPlanEndDate <= '" + FPlanEndDate_end + "'";
}

sql += " and t.FBillStatus<>0 ) t  ";                                                                                                                                                                 
sql += " left join #Data2 d2 on t.FBillID = d2.FRepairWOBillID  ";                                                                                                                                        
sql += " left join (select t.FBillID,isnull(t1.FSparePartID,0) as FSparePartID,FAuxQty,FAuxQty_base from (select t.* from RemoteServer.AIS2009.dbo.ICDeviceRepairWo t left join RemoteServer.AIS2009.dbo.ICDeviceAccount t2 on t.FDeviceID=t2.FID  where  1=1 " +
		"   "; 

/*sql += "  t2.FDeviceNumber >='YX01-13' " ;
sql += "  and t.FBillID >='MNBLL00000037'  ";
sql += " and t.FPlanBeginDate between '2015-01-01' and '2015-12-31' ";
sql += " and t.FPlanEndDate between '2015-01-01' and '2015-12-31' ";*/
if(StringUtil.isNotEmpty(FRepairWOBillID)){
	sql += " and t.FBillID = '" + FRepairWOBillID + "'";
}
if(StringUtil.isNotEmpty(FDeviceName)){
	sql += " and t2.FDeviceName like '%" + FDeviceName + "%'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_begin)){
	sql += " and t.FPlanBeginDate >= '" + FPlanBeginDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_end)){
	sql += " and t.FPlanBeginDate <= '" + FPlanBeginDate_end + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_begin)){
	sql += " and t.FPlanEndDate >= '" + FPlanEndDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_end)){
	sql += " and t.FPlanEndDate <= '" + FPlanEndDate_end + "'";
}

sql += " and t.FBillStatus<>0 ) t  left join RemoteServer.AIS2009.dbo.ICDeviceRepairwoSpare t1 on t.FID=t1.FID) t2 on t.FBillID= t2.FBillID and d2.FSparePartID=t2.FSparePartID ";                                         

sql += " select * into #data3 from #data d   left join(  select distinct t.FBillID ,isnull(t.FPlanStopManHour*d1.FTimeUnit,0) as FPlanStopManHour,isnull(t.FPlanRepairManHour*d1.FTimeUnit,0) as FPlanRepairManHour,  ";                                                                       
sql += " isnull(t.FStopManHour*d2.FTimeUnit,0) as FStopManHour,isnull(t.FRepairManHour*d2.FTimeUnit,0) as FRepairManHour  ";                                                                                                                 
sql += "  from (select t.FBillID,avg(isnull(t.FPlanStopManHour,0)) as FPlanStopManHour,avg(isnull(t.FPlanRepairManHour,0)) as FPlanRepairManHour,sum(isnull(t1.FStopManHour,0)) as FStopManHour,sum(isnull(t1.FRepairManHour,0)) as FRepairManHour ";   
sql += "  from (select t.* from RemoteServer.AIS2009.dbo.ICDeviceRepairWo t left join RemoteServer.AIS2009.dbo.ICDeviceAccount t2 on t.FDeviceID=t2.FID  where  1=1  "; 

/*sql += "  t2.FDeviceNumber >='YX01-13' " ;
sql += "  and t.FBillID >='MNBLL00000037'  "; 
sql += " and t.FPlanBeginDate between '2015-01-01' and '2015-12-31' "; 
sql += "  and t.FPlanEndDate between '2015-01-01' and '2015-12-31' "; */

if(StringUtil.isNotEmpty(FRepairWOBillID)){
	sql += " and t.FBillID = '" + FRepairWOBillID + "'";
}
if(StringUtil.isNotEmpty(FDeviceName)){
	sql += " and t2.FDeviceName like '%" + FDeviceName + "%'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_begin)){
	sql += " and t.FPlanBeginDate >= '" + FPlanBeginDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_end)){
	sql += " and t.FPlanBeginDate <= '" + FPlanBeginDate_end + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_begin)){
	sql += " and t.FPlanEndDate >= '" + FPlanEndDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_end)){
	sql += " and t.FPlanEndDate <= '" + FPlanEndDate_end + "'";
}

sql += " and t.FBillStatus<>0 ) t   ";                                                                                                                                                                                                   
sql += " left join RemoteServer.AIS2009.dbo.icdevicerepairrecord t1  on t.FBillID = t1.FRepairWOBillID and t1.FBillStatus<>0  ";                                                                                                                                      
sql += " group by t.FBillID  ) t   ";                                                                                                                                                                                                        
sql += " inner join (  select  i.FBillID,case i.FTimeUnit when 11081 then 1*1.00/60 when 11082 then 1*1 Else 1*24 end as FTimeUnit From RemoteServer.AIS2009.dbo.icdevicerepairwo i  ) d1 on t.FBillID=D1.FBillID ";                                                                                                                                                                  
sql += "  inner join (  select  r.FRepairWOBillID,case r.FTimeUnit when 11081 then 1*1.00/60 when 11082 then 1*1 Else 1*24 end as FTimeUnit From RemoteServer.AIS2009.dbo.icdevicerepairRecord r  ) d2 on t.FBillID=d2.FRepairWOBillID ";                                                                                                                                                              
sql += "  ) tmp on d.FRepairWOBillID=tmp.FBillID  "; 

sql += "  Insert into #Data3(FRepairWOBillID,FPlanStopManHour,FPlanRepairManHour,FStopManHour,FRepairManHour,FSumsort)   ";   
sql += " select '总计',sum(FPlanStopManHour),sum(FPlanRepairManHour),sum(FStopManHour),sum(FRepairManHour),100  ";           
sql += " from (  select distinct t.FBillID ,isnull(t.FPlanStopManHour*d1.FTimeUnit,0) as FPlanStopManHour,isnull(t.FPlanRepairManHour*d1.FTimeUnit,0) as FPlanRepairManHour,  ";                                                                       
sql += "   isnull(t.FStopManHour*d2.FTimeUnit,0) as FStopManHour,isnull(t.FRepairManHour*d2.FTimeUnit,0) as FRepairManHour  ";                                                                                                                 
sql += " from (select t.FBillID,avg(isnull(t.FPlanStopManHour,0)) as FPlanStopManHour,avg(isnull(t.FPlanRepairManHour,0)) as FPlanRepairManHour,sum(isnull(t1.FStopManHour,0)) as FStopManHour,sum(isnull(t1.FRepairManHour,0)) as FRepairManHour  ";  
sql += "  from (select t.* from RemoteServer.AIS2009.dbo.ICDeviceRepairWo t left join RemoteServer.AIS2009.dbo.ICDeviceAccount t2 on t.FDeviceID=t2.FID  where   1=1   "; 

/*sql += "  t2.FDeviceNumber >='YX01-13' " ;
sql += "  and t.FBillID >='MNBLL00000037'  "; 
sql += "  and t.FPlanBeginDate between '2015-01-01' and '2015-12-31' "; 
sql += " and t.FPlanEndDate between '2015-01-01' and '2015-12-31' "; */
if(StringUtil.isNotEmpty(FRepairWOBillID)){
	sql += " and t.FBillID = '" + FRepairWOBillID + "'";
}
if(StringUtil.isNotEmpty(FDeviceName)){
	sql += " and t2.FDeviceName like '%" + FDeviceName + "%'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_begin)){
	sql += " and t.FPlanBeginDate >= '" + FPlanBeginDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_end)){
	sql += " and t.FPlanBeginDate <= '" + FPlanBeginDate_end + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_begin)){
	sql += " and t.FPlanEndDate >= '" + FPlanEndDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_end)){
	sql += " and t.FPlanEndDate <= '" + FPlanEndDate_end + "'";
}

sql += " and t.FBillStatus<>0 ) t  ";                                                                                                                                                                                                    
sql += " left join RemoteServer.AIS2009.dbo.icdevicerepairrecord t1  on t.FBillID = t1.FRepairWOBillID and t1.FBillStatus<>0   ";                                                                                                                                     
sql += " group by t.FBillID  ) t  ";                                                                                                                                                                                                         
sql += " inner join (  select  i.FBillID,case i.FTimeUnit when 11081 then 1*1.00/60 when 11082 then 1*1 Else 1*24 end as FTimeUnit From RemoteServer.AIS2009.dbo.icdevicerepairwo i  ) d1 on t.FBillID=D1.FBillID ";                                                                                                                                                                 
sql += " inner join (  select  r.FRepairWOBillID,case r.FTimeUnit when 11081 then 1*1.00/60 when 11082 then 1*1 Else 1*24 end as FTimeUnit From RemoteServer.AIS2009.dbo.icdevicerepairRecord r  ) d2 on t.FBillID=d2.FRepairWOBillID  ";                                                                                                                                                             
sql += "  ) tmp  ";                                                                                      

sql += " select distinct w.FPlanBeginDate,w.FPlanEndDate,isnull(w.FBeginDate,'') FBeginDate,w.FEndDate,isnull(t1.FQtyDecimal,d3.FQtyDecimal) as FQtyDecimal ,  ";   
sql += " case when d3.FRepairPlanBillID<>' ' then '计划单' when  d3.FRepairApplyBillID<>'' then '请修单' when d3.FRepairPlanBillID=' ' and d3.FRepairApplyBillID='' then '手工录入' end as FSourceType, ";        
sql += "  case when d3.FRepairPlanBillID<>' ' then d3.FRepairPlanBillID when  d3.FRepairApplyBillID<>'' then d3.FRepairApplyBillID end as FSourceBill,  ";                                                         
sql += " case when d3.FRepairCheckResultID=-1 then '异常' when d3.FRepairCheckResultID=1 then '正常' end as FRepairCheckResult ,  ";                                                                              
sql += " t.FDeviceNumber,t.FDeviceName,t.FModel,t.FSpecification,de.FName as FDeptName,  ";                                                                                                                       
sql += " t1.FNumber as FSparePartNumber,t1.FName as FSparePartName,t1.FModel as FSparePartModel,t3.FName as FUnitName,t2.FName as FAuxUnitName,  ";                                                               
sql += " d3.FRepairWOBillID,d3.FRepairCheckResultID,d3.FSparePartID,d3.FAuxUnitID,d3.FAuxQty,d3.FAuxQty_Base,d3.FUsedQty,d3.FUsedQty_Base, ";         
sql += " d3.FStopManHour,d3.FRepairManHour,d3.FPlanStopManHour,d3.FPlanRepairManHour,FSumSort ";                         
sql += "  from #Data3 d3   ";                                                                                       
sql += " left join (select t.* from RemoteServer.AIS2009.dbo.ICDeviceRepairWo t left join RemoteServer.AIS2009.dbo.ICDeviceAccount t2 on t.FDeviceID=t2.FID  where   1=1   ";  

/*sql += "  t2.FDeviceNumber >='YX01-13' " ;
sql += "  and t.FBillID >='MNBLL00000037'  "; 
sql += " and t.FPlanBeginDate between '2015-01-01' and '2015-12-31' "; 
sql += " and t.FPlanEndDate between '2015-01-01' and '2015-12-31' "; */
if(StringUtil.isNotEmpty(FRepairWOBillID)){
	sql += " and t.FBillID = '" + FRepairWOBillID + "'";
}
if(StringUtil.isNotEmpty(FDeviceName)){
	sql += " and t2.FDeviceName like '%" + FDeviceName + "%'";
}



if(StringUtil.isNotEmpty(FPlanBeginDate_begin)){
	sql += " and t.FPlanBeginDate >= '" + FPlanBeginDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_end)){
	sql += " and t.FPlanBeginDate <= '" + FPlanBeginDate_end + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_begin)){
	sql += " and t.FPlanEndDate >= '" + FPlanEndDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_end)){
	sql += " and t.FPlanEndDate <= '" + FPlanEndDate_end + "'";
}

sql += " and t.FBillStatus<>0 ) w on d3.FRepairwobillid=w.FBillID   ";                                                   
sql += " left join RemoteServer.AIS2009.dbo.icdeviceaccount t on d3.FDeviceID=t.FID  ";                                                     
sql += " left join RemoteServer.AIS2009.dbo.t_department de on t.FDeptNumber=de.FItemID  ";                                                 
sql += " left join RemoteServer.AIS2009.dbo.t_ICItem t1 on d3.FSparePartID = t1.FItemID  ";                                                 
sql += " left join RemoteServer.AIS2009.dbo.t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID ";                                            
sql += " left join RemoteServer.AIS2009.dbo.t_MeasureUnit t2 on d3.FAuxUnitID=t2.FMeasureUnitID ";  
sql += " where FRepairWOBillID != '总计'    ";
if(StringUtil.isNotEmpty(FDeptName)){
	sql += " and de.FName = '" + FDeptName + "'";
}
sql += " order by FRepairWOBillID,t1.FNumber ";                                                                    

sql += " drop table #Data ";       
sql += " drop table #Data2 ";        
sql += " drop table #data3 ";       


		List<Map> list = equipmentMiniDao.querySBWXZX(sql);

		// List<JeecgMinidaoEntity> list =
		// jeecgMinidaoService.listAll(jeecgMinidao, dataGrid.getPage(),
		// dataGrid.getRows());

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		TagUtil.datagrid(response, dataGrid);
	}
     
	
	

	/**
	 * 
	 * 導出
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export")
	public void export(HttpServletRequest request,HttpServletResponse response, DataGrid dataGrid) {
	    

		String FRepairWOBillID = request.getParameter("FRepairWOBillID");    // 维修工单号   FDeptName
		
		String FDeptName = request.getParameter("FDeptName");    // 维修工单号 
		String FDeviceName = request.getParameter("FDeviceName");   // 设备名称
		String FPlanBeginDate_begin = request.getParameter("FPlanBeginDate_begin");   // 计划维修开始时间
		String FPlanBeginDate_end = request.getParameter("FPlanBeginDate_end");
		String FPlanEndDate_begin = request.getParameter("FPlanEndDate_begin");   // 计划维修结束日期
		String FPlanEndDate_end = request.getParameter("FPlanEndDate_end");  

String sql = " set nocount on  "; 
sql += " create table #Data  ";                                                      
sql += " (         ";                                                             
sql += " FRepairWOBillID     varchar(255)      null   ";                          
sql += " ,FRepairApplyBillID     varchar(255)  null   ";                          
sql += " ,FRePairPlanBillID  varchar(255)      null  ";                           
sql += " ,FDeviceID  int     null "; //设备ID/表ICDeiveAccount                   
sql += " ,FRepairCheckResultID    int     null ";       //验收结论               
sql += "  ,FSparePartID   int     null "; //备件代码/表t_ICItem                   
sql += " ,FAuxUnitID int    null  "; //常用计量单位/表t_Measureunit              
sql += " ,FAuxQty    decimal(28,10)  default(0) "; //常用单位标准用量            
sql += " ,FAuxQty_Base   decimal(28,10)  default(0) "; //基本单位标准用量        
sql += " ,FUsedQty   decimal(28,10)  default(0) "; //常用单位实际用量            
sql += "  ,FUsedQty_Base  decimal(28,10)  default(0) "; //基本单位实际用量        
sql += " ,FSumsort   int null  ";                                                 
sql += " ,FQtyDecimal decimal(28,10) null default(0)  ) ";                        

sql += " select t1.FRepairWOBillID,isnull(FSParePartID,0) as FSParePartID,FSpaUnit as FSpaUnit,sum(isnull(FAuxQty,0)) as FAuxQty,sum(isnull(FAuxQty_Base,0)) as FAuxQty_Base,sum(isnull(FUsedQty,0)) as FUsedQty,sum(isnull(FUsedQty_Base,0))as FUsedQty_Base  "; 
sql += "  into #data2 from RemoteServer.AIS2009.dbo.icdevicerepairrecord t1 ";                                                                                                                                                          
sql += " left join RemoteServer.AIS2009.dbo.icdevicerepairrecordspare R1  on r1.FID=t1.FID where t1.FBillStatus<>0 group by t1.FRepairWOBillID,r1.FSParePartID,r1.FSpaUnit  ";                                                           

sql += " insert into #data (FRepairWOBillID,FRepairApplyBillID,FRepairPlanBillID,FDeviceID,FRepairCheckResultID,FSparePartID,FAuxUnitID,FAuxQty,FAuxQty_Base,FUsedQty,FUsedQty_Base) ";    
sql += " select t.FBillID as FRepairWOBillID,t.FRepairApplyBillID,t.FRepairPlanBillID,t.FDeviceID,  ";                                                                                                     
sql += " isnull((select distinct FRepairCheckResult from (select FRepairWOBillID,FRepairCheckResult from RemoteServer.AIS2009.dbo.icdevicerepaircheck  ";                                                                              
sql += " where FBIllstatus<>0 and FRepairCheckDate in (select  max(FRepairCheckDate) from  RemoteServer.AIS2009.dbo.icdevicerepaircheck group by FRepairwoBillID)) c  ";                                   
sql += " where t.FBillID = c.FRepairWOBillID) ,0) as FRepairCheckResultID,   ";                                                                                                              
sql += " d2.FSparePartID,d2.FSpaUnit as FAuxUnitID,isnull(t2.FAuxQty,0) as FAuxQty,isnull(t2.FAuxQty_base,0) as FAuxQty_base,isnull(d2.FUsedQty,0) as FUsedQty,isnull(d2.FUsedQty_Base,0) as FUsedQty_Base  ";                                       
sql += " from (select t.* from RemoteServer.AIS2009.dbo.ICDeviceRepairWo t left join RemoteServer.AIS2009.dbo.ICDeviceAccount t2 on t.FDeviceID=t2.FID  where  1=1  ";  

/*sql += "  t2.FDeviceNumber >='YX01-13' " ;
sql += " and t.FBillID >='MNBLL00000037' "; 
sql += " and t.FPlanBeginDate between '2015-01-01' and '2015-12-31' ";
sql += "  and t.FPlanEndDate between '2015-01-01' and '2015-12-31' ";*/
if(StringUtil.isNotEmpty(FRepairWOBillID)){
	sql += " and t.FBillID = '" + FRepairWOBillID + "'";
}
if(StringUtil.isNotEmpty(FDeviceName)){
	sql += " and t2.FDeviceName like '%" + FDeviceName + "%'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_begin)){
	sql += " and t.FPlanBeginDate >= '" + FPlanBeginDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_end)){
	sql += " and t.FPlanBeginDate <= '" + FPlanBeginDate_end + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_begin)){
	sql += " and t.FPlanEndDate >= '" + FPlanEndDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_end)){
	sql += " and t.FPlanEndDate <= '" + FPlanEndDate_end + "'";
}

sql += " and t.FBillStatus<>0 ) t  ";                                                                                                                                                                 
sql += " left join #Data2 d2 on t.FBillID = d2.FRepairWOBillID  ";                                                                                                                                        
sql += " left join (select t.FBillID,isnull(t1.FSparePartID,0) as FSparePartID,FAuxQty,FAuxQty_base from (select t.* from RemoteServer.AIS2009.dbo.ICDeviceRepairWo t left join RemoteServer.AIS2009.dbo.ICDeviceAccount t2 on t.FDeviceID=t2.FID  where  1=1 " +
		"   "; 

/*sql += "  t2.FDeviceNumber >='YX01-13' " ;
sql += "  and t.FBillID >='MNBLL00000037'  ";
sql += " and t.FPlanBeginDate between '2015-01-01' and '2015-12-31' ";
sql += " and t.FPlanEndDate between '2015-01-01' and '2015-12-31' ";*/
if(StringUtil.isNotEmpty(FRepairWOBillID)){
	sql += " and t.FBillID = '" + FRepairWOBillID + "'";
}
if(StringUtil.isNotEmpty(FDeviceName)){
	sql += " and t2.FDeviceName like '%" + FDeviceName + "%'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_begin)){
	sql += " and t.FPlanBeginDate >= '" + FPlanBeginDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_end)){
	sql += " and t.FPlanBeginDate <= '" + FPlanBeginDate_end + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_begin)){
	sql += " and t.FPlanEndDate >= '" + FPlanEndDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_end)){
	sql += " and t.FPlanEndDate <= '" + FPlanEndDate_end + "'";
}

sql += " and t.FBillStatus<>0 ) t  left join RemoteServer.AIS2009.dbo.ICDeviceRepairwoSpare t1 on t.FID=t1.FID) t2 on t.FBillID= t2.FBillID and d2.FSparePartID=t2.FSparePartID ";                                         

sql += " select * into #data3 from #data d   left join(  select distinct t.FBillID ,isnull(t.FPlanStopManHour*d1.FTimeUnit,0) as FPlanStopManHour,isnull(t.FPlanRepairManHour*d1.FTimeUnit,0) as FPlanRepairManHour,  ";                                                                       
sql += " isnull(t.FStopManHour*d2.FTimeUnit,0) as FStopManHour,isnull(t.FRepairManHour*d2.FTimeUnit,0) as FRepairManHour  ";                                                                                                                 
sql += "  from (select t.FBillID,avg(isnull(t.FPlanStopManHour,0)) as FPlanStopManHour,avg(isnull(t.FPlanRepairManHour,0)) as FPlanRepairManHour,sum(isnull(t1.FStopManHour,0)) as FStopManHour,sum(isnull(t1.FRepairManHour,0)) as FRepairManHour ";   
sql += "  from (select t.* from RemoteServer.AIS2009.dbo.ICDeviceRepairWo t left join RemoteServer.AIS2009.dbo.ICDeviceAccount t2 on t.FDeviceID=t2.FID  where  1=1  "; 

/*sql += "  t2.FDeviceNumber >='YX01-13' " ;
sql += "  and t.FBillID >='MNBLL00000037'  "; 
sql += " and t.FPlanBeginDate between '2015-01-01' and '2015-12-31' "; 
sql += "  and t.FPlanEndDate between '2015-01-01' and '2015-12-31' "; */

if(StringUtil.isNotEmpty(FRepairWOBillID)){
	sql += " and t.FBillID = '" + FRepairWOBillID + "'";
}
if(StringUtil.isNotEmpty(FDeviceName)){
	sql += " and t2.FDeviceName like '%" + FDeviceName + "%'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_begin)){
	sql += " and t.FPlanBeginDate >= '" + FPlanBeginDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_end)){
	sql += " and t.FPlanBeginDate <= '" + FPlanBeginDate_end + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_begin)){
	sql += " and t.FPlanEndDate >= '" + FPlanEndDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_end)){
	sql += " and t.FPlanEndDate <= '" + FPlanEndDate_end + "'";
}

sql += " and t.FBillStatus<>0 ) t   ";                                                                                                                                                                                                   
sql += " left join RemoteServer.AIS2009.dbo.icdevicerepairrecord t1  on t.FBillID = t1.FRepairWOBillID and t1.FBillStatus<>0  ";                                                                                                                                      
sql += " group by t.FBillID  ) t   ";                                                                                                                                                                                                        
sql += " inner join (  select  i.FBillID,case i.FTimeUnit when 11081 then 1*1.00/60 when 11082 then 1*1 Else 1*24 end as FTimeUnit From RemoteServer.AIS2009.dbo.icdevicerepairwo i  ) d1 on t.FBillID=D1.FBillID ";                                                                                                                                                                  
sql += "  inner join (  select  r.FRepairWOBillID,case r.FTimeUnit when 11081 then 1*1.00/60 when 11082 then 1*1 Else 1*24 end as FTimeUnit From RemoteServer.AIS2009.dbo.icdevicerepairRecord r  ) d2 on t.FBillID=d2.FRepairWOBillID ";                                                                                                                                                              
sql += "  ) tmp on d.FRepairWOBillID=tmp.FBillID  "; 

sql += "  Insert into #Data3(FRepairWOBillID,FPlanStopManHour,FPlanRepairManHour,FStopManHour,FRepairManHour,FSumsort)   ";   
sql += " select '总计',sum(FPlanStopManHour),sum(FPlanRepairManHour),sum(FStopManHour),sum(FRepairManHour),100  ";           
sql += " from (  select distinct t.FBillID ,isnull(t.FPlanStopManHour*d1.FTimeUnit,0) as FPlanStopManHour,isnull(t.FPlanRepairManHour*d1.FTimeUnit,0) as FPlanRepairManHour,  ";                                                                       
sql += "   isnull(t.FStopManHour*d2.FTimeUnit,0) as FStopManHour,isnull(t.FRepairManHour*d2.FTimeUnit,0) as FRepairManHour  ";                                                                                                                 
sql += " from (select t.FBillID,avg(isnull(t.FPlanStopManHour,0)) as FPlanStopManHour,avg(isnull(t.FPlanRepairManHour,0)) as FPlanRepairManHour,sum(isnull(t1.FStopManHour,0)) as FStopManHour,sum(isnull(t1.FRepairManHour,0)) as FRepairManHour  ";  
sql += "  from (select t.* from RemoteServer.AIS2009.dbo.ICDeviceRepairWo t left join RemoteServer.AIS2009.dbo.ICDeviceAccount t2 on t.FDeviceID=t2.FID  where   1=1   "; 

/*sql += "  t2.FDeviceNumber >='YX01-13' " ;
sql += "  and t.FBillID >='MNBLL00000037'  "; 
sql += "  and t.FPlanBeginDate between '2015-01-01' and '2015-12-31' "; 
sql += " and t.FPlanEndDate between '2015-01-01' and '2015-12-31' "; */
if(StringUtil.isNotEmpty(FRepairWOBillID)){
	sql += " and t.FBillID = '" + FRepairWOBillID + "'";
}
if(StringUtil.isNotEmpty(FDeviceName)){
	sql += " and t2.FDeviceName like '%" + FDeviceName + "%'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_begin)){
	sql += " and t.FPlanBeginDate >= '" + FPlanBeginDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_end)){
	sql += " and t.FPlanBeginDate <= '" + FPlanBeginDate_end + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_begin)){
	sql += " and t.FPlanEndDate >= '" + FPlanEndDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_end)){
	sql += " and t.FPlanEndDate <= '" + FPlanEndDate_end + "'";
}

sql += " and t.FBillStatus<>0 ) t  ";                                                                                                                                                                                                    
sql += " left join RemoteServer.AIS2009.dbo.icdevicerepairrecord t1  on t.FBillID = t1.FRepairWOBillID and t1.FBillStatus<>0   ";                                                                                                                                     
sql += " group by t.FBillID  ) t  ";                                                                                                                                                                                                         
sql += " inner join (  select  i.FBillID,case i.FTimeUnit when 11081 then 1*1.00/60 when 11082 then 1*1 Else 1*24 end as FTimeUnit From RemoteServer.AIS2009.dbo.icdevicerepairwo i  ) d1 on t.FBillID=D1.FBillID ";                                                                                                                                                                 
sql += " inner join (  select  r.FRepairWOBillID,case r.FTimeUnit when 11081 then 1*1.00/60 when 11082 then 1*1 Else 1*24 end as FTimeUnit From RemoteServer.AIS2009.dbo.icdevicerepairRecord r  ) d2 on t.FBillID=d2.FRepairWOBillID  ";                                                                                                                                                             
sql += "  ) tmp  ";                                                                                      

sql += " select distinct Convert(varchar(50),w.FPlanBeginDate,23) FPlanBeginDate,Convert(varchar(50),w.FPlanEndDate,23) FPlanEndDate,case when w.FBeginDate = null then '' else Convert(varchar(50),w.FBeginDate,23) end as FBeginDate,Convert(varchar(50),w.FEndDate,23) FEndDate,isnull(t1.FQtyDecimal,d3.FQtyDecimal) as FQtyDecimal ,  ";   
sql += " case when d3.FRepairPlanBillID<>' ' then '计划单' when  d3.FRepairApplyBillID<>'' then '请修单' when d3.FRepairPlanBillID=' ' and d3.FRepairApplyBillID='' then '手工录入' end as FSourceType, ";        
sql += "  case when d3.FRepairPlanBillID<>' ' then d3.FRepairPlanBillID when  d3.FRepairApplyBillID<>'' then d3.FRepairApplyBillID end as FSourceBill,  ";                                                         
sql += " case when d3.FRepairCheckResultID=-1 then '异常' when d3.FRepairCheckResultID=1 then '正常' end as FRepairCheckResult ,  ";                                                                              
sql += " t.FDeviceNumber,t.FDeviceName,t.FModel,t.FSpecification,de.FName as FDeptName,  ";                                                                                                                       
sql += " t1.FNumber as FSparePartNumber,t1.FName as FSparePartName,t1.FModel as FSparePartModel,t3.FName as FUnitName,t2.FName as FAuxUnitName,  ";                                                               
sql += " d3.FRepairWOBillID,d3.FRepairCheckResultID,d3.FSparePartID,d3.FAuxUnitID,d3.FAuxQty,d3.FAuxQty_Base,d3.FUsedQty,d3.FUsedQty_Base, ";         
sql += " d3.FStopManHour,d3.FRepairManHour,d3.FPlanStopManHour,d3.FPlanRepairManHour,FSumSort ";                         
sql += "  from #Data3 d3   ";                                                                                       
sql += " left join (select t.* from RemoteServer.AIS2009.dbo.ICDeviceRepairWo t left join RemoteServer.AIS2009.dbo.ICDeviceAccount t2 on t.FDeviceID=t2.FID  where   1=1   ";  

/*sql += "  t2.FDeviceNumber >='YX01-13' " ;
sql += "  and t.FBillID >='MNBLL00000037'  "; 
sql += " and t.FPlanBeginDate between '2015-01-01' and '2015-12-31' "; 
sql += " and t.FPlanEndDate between '2015-01-01' and '2015-12-31' "; */
if(StringUtil.isNotEmpty(FRepairWOBillID)){
	sql += " and t.FBillID = '" + FRepairWOBillID + "'";
}
if(StringUtil.isNotEmpty(FDeviceName)){
	sql += " and t2.FDeviceName like '%" + FDeviceName + "%'";
}



if(StringUtil.isNotEmpty(FPlanBeginDate_begin)){
	sql += " and t.FPlanBeginDate >= '" + FPlanBeginDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanBeginDate_end)){
	sql += " and t.FPlanBeginDate <= '" + FPlanBeginDate_end + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_begin)){
	sql += " and t.FPlanEndDate >= '" + FPlanEndDate_begin + "'";
}
if(StringUtil.isNotEmpty(FPlanEndDate_end)){
	sql += " and t.FPlanEndDate <= '" + FPlanEndDate_end + "'";
}

sql += " and t.FBillStatus<>0 ) w on d3.FRepairwobillid=w.FBillID   ";                                                   
sql += " left join RemoteServer.AIS2009.dbo.icdeviceaccount t on d3.FDeviceID=t.FID  ";                                                     
sql += " left join RemoteServer.AIS2009.dbo.t_department de on t.FDeptNumber=de.FItemID  ";                                                 
sql += " left join RemoteServer.AIS2009.dbo.t_ICItem t1 on d3.FSparePartID = t1.FItemID  ";                                                 
sql += " left join RemoteServer.AIS2009.dbo.t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID ";                                            
sql += " left join RemoteServer.AIS2009.dbo.t_MeasureUnit t2 on d3.FAuxUnitID=t2.FMeasureUnitID ";  
sql += " where FRepairWOBillID != '总计'    ";
if(StringUtil.isNotEmpty(FDeptName)){
	sql += " and de.FName = '" + FDeptName + "'";
}
sql += " order by FRepairWOBillID,t1.FNumber ";                                                                    

sql += " drop table #Data ";       
sql += " drop table #Data2 ";        
sql += " drop table #data3 ";    
		List<Map> maplist = null;
		//List<Map> list = equipmentMiniDao.querySBWXZX(sql);
		try
		{
			maplist =equipmentMiniDao.querySBWXZX(sql);
			List<String[]> list = new ArrayList<String[]>();
			
			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FRepairWOBillID");
				arr[1] = (String) map.get("FSourceBill");
				arr[2] = (String) map.get("FDeviceNumber");
				arr[3] = (String) map.get("FDeviceName");
				arr[4] = (String) map.get("FDeptName");
				arr[5] = (String) map.get("FPlanBeginDate");
				arr[6] = (String) map.get("FPlanEndDate");
				arr[7] = (String) map.get("FBeginDate");
				arr[8] = (String) map.get("FEndDate");
				arr[9] =(String) map.get("FRepairCheckResult");
				arr[10] =  (String) map.get("FRepairManHour");
				list.add(arr);
				 
			}
		
			String[] columnNames = new String[] { "维修工单号", "来源单据", "设备代码", "设备名称","使用部门","计划维修开始日期","计划维修结束日期","实际维修开始日期","实际维修结束日期","验收结论","实际维修工时"};
	       String titleName = "设备维修执行表.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

	        ExcelUtils.create(request, list, columnNames, titleName, fOut);
			
		} catch (Exception ex)
		{

		}
		
	
	}
	
	
}
